package com.pingan.haofang.searchcloud.monitor.dao;

import com.pingan.haofang.searchcloud.monitor.constants.WarningStatus;
import com.pingan.haofang.searchcloud.monitor.convert.ColumnToBean;
import com.pingan.haofang.searchcloud.monitor.domain.DashboardWarningConfig;
import com.pingan.haofang.searchcloud.monitor.dto.WarningViewQueryDTO;
import com.pingan.haofang.searchcloud.monitor.dto.WarningViewQueryResult;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.SQLQuery;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.stereotype.Repository;

import javax.persistence.EntityManager;
import javax.persistence.Query;
import java.math.BigInteger;
import java.util.List;

/**
 * 搜索云预警配置
 *
 * @author EX-ZHOUYONG005
 * @date 2018/10/19
 * @since 1.0.0
 */
@Repository
public interface DashboardWarningConfigDao
        extends JpaRepository<DashboardWarningConfig, Long>, JpaSpecificationExecutor<DashboardWarningConfig> {

    default List<WarningViewQueryResult> queryWarningView(WarningViewQueryDTO dto, EntityManager entityManager) {

        StringBuilder sqlBuilder = new StringBuilder("SELECT wc.id as id, wc.project_id as projectId, "
                + " wc.project_name as projectName, "
                + " wc.index_id as indexId, wc.index_name as indexName, "
                + " wc.warning_config as warningConfig, wc.mail_to as mailTo, "
                + " COUNT(wh.config_id) as waringNum FROM t_dashboard_warning_config wc "
                + " LEFT JOIN t_dashboard_warning_history wh ON wc.id = wh.config_id ");

        sqlBuilder = appendWhereSQL(sqlBuilder, dto);

        int fromIndex = (dto.getPageNo() - 1) * dto.getPageSize();
        sqlBuilder.append(" LIMIT " + fromIndex + "," + dto.getPageSize());
        Query query = entityManager.createNativeQuery(sqlBuilder.toString());

        List<WarningViewQueryResult> datas = query.unwrap(SQLQuery.class)
                .setResultTransformer(new ColumnToBean(WarningViewQueryResult.class)).list();
        return datas;
    }

    default StringBuilder appendWhereSQL(StringBuilder sqlBuilder, WarningViewQueryDTO dto) {
        sqlBuilder.append(" WHERE wc.status = 0");

        int warningType = dto.getWarningType();
        sqlBuilder.append(" and wc.warning_type=" + warningType);

        Long projectId = dto.getProjectId();
        if (projectId != null && projectId != -1) {
            sqlBuilder.append(" and wc.project_id=" + projectId);
        }

        String waringUser = dto.getWaringUser();
        if (StringUtils.isNotBlank(waringUser)) {
            sqlBuilder.append(" and wc.mail_to like '%" + dto.getWaringUser() + "%'");
        }

        String startDate = dto.getStartDate();
        if (StringUtils.isNotBlank(startDate)) {
            sqlBuilder.append(" and wh.create_time>='" + startDate + "'");
        }

        String endDate = dto.getEndDate();
        if (StringUtils.isNotBlank(endDate)) {
            sqlBuilder.append(" and wh.create_time<='" + endDate + "'");
        }

        sqlBuilder.append(" GROUP BY wc.project_id, wc.project_name, "
                + " wc.index_id, wc.index_name, wc.warning_config, wc.mail_to");

        Integer status = dto.getStatus();
        if (status != null) {
            if (status == WarningStatus.YES.getValue()) {
                sqlBuilder.append(" HAVING waringNum > 0");
            } else if (status == WarningStatus.NOT.getValue()) {
                sqlBuilder.append(" HAVING waringNum = 0");
            }
        }
        return sqlBuilder;
    }

    List<DashboardWarningConfig> findByWarningTypeAndStatus(int warningType, int status);

    default int queryWarningCount(WarningViewQueryDTO dto, EntityManager entityManager) {
        StringBuilder sqlBuilder = new StringBuilder("SELECT wc.id AS id, COUNT(wh.config_id) AS waringNum FROM t_dashboard_warning_config wc LEFT JOIN t_dashboard_warning_history wh ON wc.id = wh.config_id");
        sqlBuilder = appendWhereSQL(sqlBuilder, dto);
        String sql = "SELECT COUNT(tmp.id) AS total FROM ( " + sqlBuilder.toString() + " ) AS tmp";
        Query query = entityManager.createNativeQuery(sql);
        BigInteger count = (BigInteger) query.getSingleResult();
        return count.intValue();
    }
}
